'''
* PythonName: getWeeather
* Description: 获取外部环境信息，保存至数据库（SQL SERVER）
* @Author Annekko(Sunjk)
* @Create 2025/2/5 19：47
* @Version 3.0
'''
import csv
from datetime import datetime, date
import time
import requests

import pymssql



DB_CONFIG = {
    'driver': 'ODBC Driver 17 for SQL Server',
    'server': 'DESKTOP-65OGBP4',
    'user': 'sa',
    'password': '123456',
    'database': 'bibmcse',
    # 'port': 3306
}

WEATHER_TYPE_MAPPING = {
    '温度': 1,
    '湿度': 2,
    '风向': 3,
    '风力': 4,
    '空气质量': 5,
    'PM10': 6,
    'PM2.5': 7,
    'CO': 8,
    'NO2': 9,
    'SO2': 10,
    '日出时间': 11,
    '日落时间': 12,
    '风速': 13,
    '空气质量等级': 15,
    '主要污染物': 16,
    '大气压强': 17
}
def get_db_connection():
    # conn_str = f"DRIVER={DB_CONFIG['driver']};SERVER={DB_CONFIG['server']};DATABASE={DB_CONFIG['database']};UID={DB_CONFIG['user']};PWD={DB_CONFIG['password']}"
    return pymssql.connect('127.0.0.1','sa','123456','bibmcse')


from datetime import datetime


def parse_value(value_str, field_name):
    try:
        # 处理时间类型字段
        if 'T' in value_str and '+' in value_str:
            return datetime.fromisoformat(value_str).strftime("%Y-%m-%d %H:%M:%S"), None
        if ':' in value_str:
            return datetime.strptime(value_str, "%H:%M").strftime("%H:%M:%S"), None

        if field_name == '温度':
            return float(value_str), '°C'
        elif field_name == '湿度':
            return float(value_str), '%'
        elif field_name in ['风力', '风速']:
            return float(value_str), '级'
        elif field_name in ['PM10', 'PM2.5']:
            return float(value_str), 'μg/m³'
        elif field_name == 'CO':
            return float(value_str), 'mg/m³'
        elif field_name in ['NO2', 'SO2']:
            return float(value_str), 'μg/m³'
        elif field_name == '大气压强':
            return float(value_str), 'hPa'

        # 处理纯数字但无标准单位的字段
        if value_str.isdigit() or value_str.replace('.', '', 1).isdigit():
            return float(value_str), None

        # 风向等不需要单位的字段
        return value_str, None

    except Exception as e:
        print(f"解析值 '{value_str}' 时出错: {e}")
        return None, None



def transform_weather_data(raw_data, site):
    batch_data = []
    current_time = datetime.now()

    for field, value in raw_data.items():
        if field not in WEATHER_TYPE_MAPPING:
            continue

        type_id = WEATHER_TYPE_MAPPING[field]
        data_value, unit = parse_value(str(value),field)

        if data_value is None:
            print(f"Field '{field}' has no valid value, skipping...")
            continue

        batch_data.append((
            type_id,
            field,  # MeteorologicalDataType
            data_value,
            unit,
            current_time,
            site
        ))

    return batch_data



def save_to_database(weather_records):

    if not weather_records:
        return

    conn = None
    try:
        conn = get_db_connection()
        if conn:
            print("连接到数据库成功")
        cursor = conn.cursor()

        sql = '''INSERT INTO [dbo].[MeteorologicalData] 
                ([MeteorologicalDataTypeID], [MeteorologicalDataType],
                 [MeteorologicalDataValue], [MeteorologicalDataUnit],
                 [MeteorologicalDataTime])
                VALUES (%s, %s, %s, %s, %s)'''

        cursor.executemany(sql, weather_records)
        conn.commit()
        print(f"成功插入 {len(weather_records)} 条记录")

    except pymssql.DatabaseError as e:
        print(f"数据库错误: {str(e)}")
        if conn:
            conn.rollback()
    except Exception as e:
        print(f"系统错误: {str(e)}")
    finally:
        if conn:
            conn.close()


def get_weather_now(site):
    """获取天气数据"""
    try:
        with open('./China-City-List-latest.csv', 'rt', encoding='utf-8') as f:
            reader = csv.reader(f)
            for row in reader:
                if site in row[13]:
                    location_id = row[0]
                    area = row[2]
        weather_res = requests.get(
            f'https://mu78kx4r8r.re.qweatherapi.com/v7/weather/now?location={location_id}&key=54928c11c91b4e51ba4459cbc133d06f',
            timeout=15
        )
        weather_data = weather_res.json().get('now', {})
        air_quality_res = requests.get(
            f'https://mu78kx4r8r.re.qweatherapi.com/v7/air/now?location={location_id}&key=54928c11c91b4e51ba4459cbc133d06f',
            timeout=15
        )
        air_data = air_quality_res.json().get('now', {})

        today = date.today().strftime('%Y%m%d')
        print(f"today: {today}")
        print(f"location_id: {location_id}")
        sun_res = requests.get(
            'https://mu78kx4r8r.re.qweatherapi.com/v7/astronomy/sun?location={}&date={}&key=54928c11c91b4e51ba4459cbc133d06f'.format(location_id,today),
            timeout=15
        )
        sun_data = sun_res.json()

        raw_data = {
            '温度': f"{weather_data.get('temp', '')}",
            '湿度': f"{weather_data.get('humidity', '')}",
            '风向': weather_data.get('windDir', ''),
            '风力': f"{weather_data.get('windScale', '')}",
            '空气质量': air_data.get('aqi', ''),
            'PM10': air_data.get('pm10', ''),
            'PM2.5': air_data.get('pm2p5', ''),
            'CO': air_data.get('co', ''),
            'NO2': air_data.get('no2', ''),
            'SO2': air_data.get('so2', ''),
            '日出时间': sun_data.get('sunrise', ''),
            '日落时间': sun_data.get('sunset', ''),
            '风速': weather_data.get('windSpeed', ''),
            '空气质量等级': air_data.get('category', ''),
            '主要污染物': air_data.get('primary', ''),
            '大气压强': weather_data.get('pressure', '')
        }
        print(f"Raw data for {site}: {raw_data}")

        return raw_data

    except requests.exceptions.RequestException as e:
        print(f"API请求失败: {str(e)}")
        return {}
    except Exception as e:
        print(f"数据处理错误: {str(e)}")
        return {}




def get_sites_from_dbo():
    conn2 = None
    sites = []
    try:
        conn2 = pymssql.connect(host='127.0.0.1', user='sa', password='123456', database='bibmcse')
        if conn2:
            print("连接到数据库成功")
        cursor2 = conn2.cursor()

        query = """
        SELECT LEFT([EnterpriseCode], 6)
        FROM [dbo].[Enterprise]
        WHERE [AdministrativeDivisionCode] = LEFT([EnterpriseCode], 6)
        """
        cursor2.execute(query)
        for row in cursor2:
            sites.append(row[0])
    except pymssql.DatabaseError as e:
        print(f"数据库错误: {str(e)}")
    except Exception as e:
        print(f"系统错误: {str(e)}")
    finally:
        if conn2:
            conn2.close()
    return sites


def main():
    sites = get_sites_from_dbo()
    if not sites:  # 更严谨的判断空列表
        print("无法获取站点信息或站点列表为空")
        return

    print("获取到的站点列表:", sites)

    all_records = []
    previous_site = None  # 用于记录上一个处理的站点

    for site in sites:
        if site == previous_site:  # 如果当前站点与上一个相同，则跳过

            continue

        print(f"正在采集 {site} 数据...")
        raw_data = get_weather_now(site)

        if raw_data:
            records = transform_weather_data(raw_data, site)
            all_records.extend(records)
            previous_site = site  # 更新上一个处理的站点
        else:
            print(f"未能获取 {site} 的天气数据")

    if all_records:
        save_to_database(all_records)
        print(f"成功保存 {len(all_records)} 条天气数据记录")
    else:
        print("未获取到任何有效数据")


if __name__ == '__main__':
    main()



